package com.iflytek.esb.workflow.spi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;

import com.iflytek.esb.domain.Id;
import com.iflytek.esb.workflow.po.PreviousStepData;
import com.iflytek.esb.workflow.po.WorkflowEntryData;
import com.iflytek.esb.workflow.po.WorkflowStepData;
import com.opensymphony.module.propertyset.PropertySet;
import com.opensymphony.module.propertyset.PropertySetManager;

import com.opensymphony.workflow.StoreException;
import com.opensymphony.workflow.query.Expression;
import com.opensymphony.workflow.query.FieldExpression;
import com.opensymphony.workflow.query.NestedExpression;
import com.opensymphony.workflow.query.WorkflowExpressionQuery;
import com.opensymphony.workflow.query.WorkflowQuery;
import com.opensymphony.workflow.spi.SimpleStep;
import com.opensymphony.workflow.spi.SimpleWorkflowEntry;
import com.opensymphony.workflow.spi.Step;
import com.opensymphony.workflow.spi.WorkflowEntry;
import com.opensymphony.workflow.spi.WorkflowStore;

public class MybatisWorkflowStore implements WorkflowStore {
	
	private final static Logger log = Logger.getLogger(MybatisWorkflowStore.class);
	
	protected SqlSessionFactory sessionFactory;
	protected boolean closeConnWhenDone = true;

	public void setSessionFactory(SqlSessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

    public void setEntryState(long id, int state) throws StoreException {
        try {
        	WorkflowEntryData data = new WorkflowEntryData();
        	data.setId(id);
        	data.setState(state);
        	sessionFactory.openSession().update("updateWorkflowEntry", data);
        	log.debug("set wf entry state, id: " + id + ", state: " + state);
        } catch (Exception e) {
            throw new StoreException("Unable to update state for workflow instance #" + id + " to " + state, e);
        }
    }

    public PropertySet getPropertySet(long entryId) {
        HashMap args = new HashMap(1);
        args.put("globalKey", "os_" + entryId);
        args.put("sessionFactory", sessionFactory);

        return PropertySetManager.getInstance("mybatis", args);
    }

    ////////////METHOD #2 OF 3 //////////////////
    ////////// ...gur;  ////////////////////
    //kiz
    public boolean checkIfORExists(NestedExpression nestedExpression) {
        //GURKAN;
        //This method checks if OR exists in any nested query
        //This method is used by doNestedNaturalJoin() to make sure
        //OR does not exist within query
        int numberOfExp = nestedExpression.getExpressionCount();

        if (nestedExpression.getExpressionOperator() == NestedExpression.OR) {
            return true;
        }

        for (int i = 0; i < numberOfExp; i++) {
            Expression expression = nestedExpression.getExpression(i);

            if (expression.isNested()) {
                NestedExpression nestedExp = (NestedExpression) expression;

                return checkIfORExists(nestedExp);
            }
        }

        //System.out.println("!!!...........false is returned ..!!!");
        return false;
    }

    public Step createCurrentStep(long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status, long[] previousIds) throws StoreException {
        try {
            long id = createCurrentStep(entryId, wfStepId, owner, startDate, dueDate, status);
            addPreviousSteps(id, previousIds);

            return new SimpleStep(id, entryId, wfStepId, 0, owner, startDate, dueDate, null, status, previousIds, null);
        } catch (Exception e) {
            throw new StoreException("Unable to create current step for workflow instance #" + entryId, e);
        }
    }

    public WorkflowEntry createEntry(String workflowName) throws StoreException {
        try {
        	WorkflowEntryData data = new WorkflowEntryData();
            long id = getNextEntrySequence();
        	data.setId(id);
        	data.setName(workflowName);
        	data.setState(WorkflowEntry.CREATED);
        	sessionFactory.openSession().insert("insertWorkflowEntry", data);
        	
        	log.debug("inserted new wf entry, id: " + id + ", name: " + workflowName);
            return new SimpleWorkflowEntry(id, workflowName, WorkflowEntry.CREATED);
        } catch (Exception e) {
            throw new StoreException("Error creating new workflow instance", e);
        }
    }

    public List findCurrentSteps(long entryId) throws StoreException {
        try {
        	List stepList = sessionFactory.openSession().selectList("selectCurrentStepByEntry", new Long(entryId));
        	log.debug("get step by entry id: " + entryId + ", count: " + stepList.size());

            ArrayList currentSteps = new ArrayList();
            for (int i = 0; i < stepList.size(); i ++) {
            	WorkflowStepData stepData = (WorkflowStepData)stepList.get(i);
                long id = stepData.getId().longValue();
                int stepId = stepData.getStepId().intValue();
                int actionId = stepData.getActionId() == null ? 0 : stepData.getActionId().intValue();
                String owner = stepData.getOwner();
                Date startDate = stepData.getStartDate();
                Date dueDate = stepData.getDueDate();
                Date finishDate = stepData.getFinishDate();
                String status = stepData.getStatus();
                String caller = stepData.getCaller();
            	log.debug("step data, id: " + stepData.getId());
            	
            	List prevList = sessionFactory.openSession().selectList("selectCurrentPriviousStep", new Long(stepData.getId()));
            	long[] prevIds = new long[prevList.size()];
            	log.debug("find prev steps, count: " + prevList.size());
            	
            	for (int j = 0; j < prevList.size(); j ++) {
            		PreviousStepData p = (PreviousStepData)prevList.get(j); 
            		prevIds[j] = p.getPreviousId();
            	}
            	
            	SimpleStep step = new SimpleStep(id, entryId, stepId, actionId, owner, startDate, dueDate, finishDate, status, prevIds, caller);
            	currentSteps.add(step);
            }

            return currentSteps;
        } catch (Exception e) {
            throw new StoreException("Unable to locate current steps for workflow instance #" + entryId, e);
        }
    }

    public WorkflowEntry findEntry(long theEntryId) throws StoreException {
        try {
        	WorkflowEntryData data = (WorkflowEntryData)sessionFactory.openSession().selectOne(
        			"selectWorkflowEntry", new Long(theEntryId));

            String workflowName = data.getName();
            int state = data.getState();

            return new SimpleWorkflowEntry(theEntryId, workflowName, state);
        } catch (Exception e) {
            throw new StoreException("Error finding workflow instance #" + theEntryId);
        }
    }

    public List findHistorySteps(long entryId) throws StoreException {
        try {
        	List stepList = sessionFactory.openSession().selectList("selectHistoryStepByEntry", new Long(entryId));
        	log.debug("select history step by entry id, count: " + stepList.size());
        	
            ArrayList currentSteps = new ArrayList();
            for (int i = 0; i < stepList.size(); i ++) {
            	WorkflowStepData stepData = (WorkflowStepData)stepList.get(i);
            	
            	List prevList = sessionFactory.openSession().selectList("selectHistoryPriviousStep", new Long(stepData.getId()));
            	long[] prevIds = new long[prevList.size()];
            	
            	for (int j = 0; j < prevList.size(); j ++) {
            		PreviousStepData p = (PreviousStepData)prevList.get(j); 
            		prevIds[j] = p.getPreviousId();
            	}
            	
                SimpleStep step = new SimpleStep(
                		stepData.getId(), 
                		stepData.getEntryId(), 
                		stepData.getStepId(), 
                		stepData.getActionId(), 
                		stepData.getOwner(), 
                		stepData.getStartDate(), 
                		stepData.getDueDate(), 
                		stepData.getFinishDate(), 
                		stepData.getStatus(), 
                		prevIds, 
                		stepData.getCaller());
                currentSteps.add(step);
            }
            return currentSteps;
        } catch (Exception e) {
            throw new StoreException("Unable to locate history steps for workflow instance #" + entryId, e);
        }
    }

    public void init(Map props) {
    }

    public Step markFinished(Step step, int actionId, Date finishDate, String status, String caller) throws StoreException {
        try {
        	WorkflowStepData data = new WorkflowStepData();
        	data.setStatus(status);
        	data.setActionId(actionId);
        	data.setFinishDate(finishDate);
        	data.setCaller(caller);
        	data.setId(step.getId());
        	sessionFactory.openSession().update("updateCurrentStep", data);
        	log.debug("current step updated");

            SimpleStep theStep = (SimpleStep) step;
            theStep.setActionId(actionId);
            theStep.setFinishDate(finishDate);
            theStep.setStatus(status);
            theStep.setCaller(caller);

            return theStep;
        } catch (Exception e) {
            throw new StoreException("Unable to mark step finished for #" + step.getEntryId(), e);
        }
    }

    public void moveToHistory(Step step) throws StoreException {
        try {
        	WorkflowStepData stepData = new WorkflowStepData();
        	stepData.setId(step.getId());
        	stepData.setEntryId(step.getEntryId());
        	stepData.setStepId(step.getStepId());
        	stepData.setActionId(step.getActionId());
        	stepData.setOwner(step.getOwner());
        	stepData.setStartDate(step.getStartDate());
        	stepData.setFinishDate(step.getFinishDate());
        	stepData.setStatus(step.getStatus());
        	stepData.setCaller(step.getCaller());
        	sessionFactory.openSession().insert("insertHistoryStep", stepData);

        	log.debug("inserted history step, id: " + step.getId());
            long[] previousIds = step.getPreviousStepIds();

            if ((previousIds != null) && (previousIds.length > 0)) {
                log.debug("insert previous history step, count: " + previousIds.length);

                for (int i = 0; i < previousIds.length; i++) {
                	PreviousStepData preData = new PreviousStepData();
                	preData.setId(step.getId());
                	preData.setPreviousId(previousIds[i]);
                	sessionFactory.openSession().insert("insertHistoryPriviousStep", preData);
                }
            }

            log.debug("delete current prev step: " + step.getId());
            sessionFactory.openSession().delete("deleteCurrentPriviousStep", new Long(step.getId()));

            log.debug("delete current step: " + step.getId());
            sessionFactory.openSession().delete("deleteCurrentStep", new Long(step.getId()));
        } catch (Exception e) {
            throw new StoreException("Unable to move current step to history step for #" + step.getEntryId(), e);
        }
    }

    public List query(WorkflowExpressionQuery e) throws StoreException {
        //GURKAN;
        // If it is simple, call buildSimple()
        //  SELECT DISTINCT(ENTRY_ID) FROM os_historystep WHERE FINISH_DATE < ?
        //
        // If it is nested, call doNestedNaturalJoin() if and only if the query is
        // ANDed including nested-nestd queries
        // If OR exists in any query call buildNested()
        //
        //doNestedNaturalJoin()
        //  This doNestedNaturalJoin() method improves performance of the queries if and only if
        //  the queries including nested queries are ANDed
        //
        //	SELECT DISTINCT (a1.ENTRY_ID) AS retrieved
        //		FROM os_currentstep AS a1 , os_currentstep AS a2 , os_currentstep AS a3 , os_currentstep AS a4
        //			WHERE ((a1.ENTRY_ID = a1.ENTRY_ID AND a1.ENTRY_ID = a2.ENTRY_ID) AND
        //					 (a2.ENTRY_ID = a3.ENTRY_ID AND a3.ENTRY_ID = a4.ENTRY_ID))
        //				AND ( a1.OWNER =  ?  AND a2.STATUS !=  ?  AND a3.OWNER =  ?  AND a4.STATUS !=  ?  )
        //
        //doNestedLeftJoin() //not used
        //  For this method to work, order of queries is matter
        //  This doNestedLeftJoin() method will generate the queries but it works if and only if
        //  the query is in correct order -- it is your luck
        //                SELECT DISTINCT (a0.ENTRY_ID) AS retrieved FROM os_currentstep AS a0
        //                                LEFT JOIN os_currentstep a1  ON a0.ENTRY_ID = a1.ENTRY_ID
        //
        //                                LEFT JOIN os_currentstep a2  ON a1.ENTRY_ID = a2.ENTRY_ID
        //                                LEFT JOIN os_currentstep a3  ON a2.ENTRY_ID = a3.ENTRY_ID
        //                                                WHERE a1.OWNER =  ? AND (a2.STATUS =  ?  OR a3.OWNER =  ?)
        //
        if (log.isDebugEnabled()) {
            log.debug("Starting Query");
        }

        Expression expression = e.getExpression();

        if (log.isDebugEnabled()) {
            log.debug("Have all variables");
        }

        if (expression.isNested()) {
            NestedExpression nestedExp = (NestedExpression) expression;

            StringBuffer sel = new StringBuffer();
            StringBuffer columns = new StringBuffer();
            StringBuffer leftJoin = new StringBuffer();
            StringBuffer where = new StringBuffer();
            StringBuffer whereComp = new StringBuffer();
            StringBuffer orderBy = new StringBuffer();
            List values = new LinkedList();
            List queries = new LinkedList();

            String columnName;
            String selectString;

            //Expression is nested and see if the expresion has OR
            if (checkIfORExists(nestedExp)) {
                //For doNestedLeftJoin() uncomment these -- again order is matter
                //and comment out last two lines where buildNested() is called
                //
                //columns.append("SELECT DISTINCT (");
                //columns.append("a0" + "." + stepEntryId);
                //columnName = "retrieved";
                //columns.append(") AS " + columnName);
                //columns.append(" FROM ");
                //columns.append(currentTable + " AS " + "a0");
                //where.append("WHERE ");
                //doNestedLeftJoin(e, nestedExp, leftJoin, where, values, queries, orderBy);
                //selectString = columns.toString() + " " + leftJoin.toString() + " " + where.toString() + " " + orderBy.toString();
                //System.out.println("LEFT JOIN ...");
                //
                //
                columnName = buildNested(nestedExp, sel, values);
                selectString = sel.toString();
            } else {
                columns.append("SELECT DISTINCT (");
                columns.append("a1.ENTRY_ID");
                columnName = "retrieved";
                columns.append(") AS " + columnName);
                columns.append(" FROM ");
                where.append("WHERE ");

                doNestedNaturalJoin(e, nestedExp, columns, where, whereComp, values, queries, orderBy);
                selectString = columns.toString() + ' ' + leftJoin.toString() + ' ' + where.toString() + " AND ( " + whereComp.toString() + " ) " + ' ' + orderBy.toString();

                //              System.out.println("NATURAL JOIN ...");
            }

            //System.out.println("number of queries is      : " + queries.size());
            //System.out.println("values.toString()         : " + values.toString());
            //System.out.println("columnName                : " + columnName);
            //System.out.println("where                     : " + where);
            //System.out.println("whereComp                 : " + whereComp);
            //System.out.println("columns                   : " + columns);
            //          System.out.println("Query is : " + selectString + "\n");
            return doExpressionQuery(selectString, columnName, values);
        } else {
            // query is not empty ... it's a SIMPLE query
            // do what the old query did
            StringBuffer qry;
            List values = new LinkedList();

            qry = new StringBuffer();

            String columnName = buildSimple((FieldExpression) expression, qry, values);

            if (e.getSortOrder() != WorkflowExpressionQuery.SORT_NONE) {
                qry.append(" ORDER BY ");

                if (e.getOrderBy() != 0) {
                    String fName = fieldName(e.getOrderBy());

                    qry.append(fName);

                    // To help w/ MySQL and Informix, you have to include the column in the query
                    String current = qry.toString();
                    String entry = current.substring(0, current.indexOf(columnName)) + columnName + "), " + fName + ' ';
                    entry += current.substring(current.indexOf(columnName) + columnName.length() + 1);

                    qry = new StringBuffer(entry);

                    if (e.getSortOrder() == WorkflowExpressionQuery.SORT_DESC) {
                        qry.append(" DESC");
                    } else {
                        qry.append(" ASC");
                    }
                } else {
                    qry.append(columnName);
                }
            }

            //System.out.println("Query is: " + qry.toString());
            return doExpressionQuery(qry.toString(), columnName, values);
        }
    }

    public List query(WorkflowQuery query) throws StoreException {
        List results = null;

        // going to try to do all the comparisons in one query
        String sel;
        String table;

        int qtype = query.getType();

        if (qtype == 0) { // then not set, so look in sub queries
                          // todo: not sure if you would have a query that would look in both old and new, if so, i'll have to change this - TR
                          // but then again, why are there redundant tables in the first place? the data model should probably change

	        if (query.getLeft() != null) {
	            qtype = query.getLeft().getType();
	        }
        }

        if (qtype == WorkflowQuery.CURRENT) {
            table = "os_currentstep";
        } else {
            table = "os_historystep";
        }

        sel = "SELECT DISTINCT(ENTRY_ID) AS retrieved FROM " + table + " WHERE ";
        sel += queryWhere(query);

        try {
        	results = sessionFactory.openSession().selectList("queryWorkflowEntry", sel);
        } catch (Exception ex) {
            throw new StoreException("SQL Exception in query: " + ex.getMessage());
        }

        return results;
    }
    
    protected long getNextEntrySequence() throws StoreException {
        try {
        	Id id = new Id();
        	sessionFactory.openSession().insert("insertEntrySequence", id);
        	return id.getMaxId();
        } catch (Exception e) {
        	throw new StoreException("SQL Exception in get entry sequence: " + e.getMessage());
        }
    }

    protected long getNextStepSequence() throws StoreException {
        try {
        	Id id = new Id();
        	sessionFactory.openSession().insert("insertStepSequence", id);
        	return id.getMaxId();
        } catch (Exception e) {
        	throw new StoreException("SQL Exception in get step sequence: " + e.getMessage());
        }
    }

    protected void addPreviousSteps(long id, long[] previousIds) throws StoreException {
        if ((previousIds != null) && (previousIds.length > 0)) {
            if (!((previousIds.length == 1) && (previousIds[0] == 0))) {
            	try {
	                for (int i = 0; i < previousIds.length; i++) {
	                	PreviousStepData data = new PreviousStepData();
	                	data.setId(id);
	                	data.setPreviousId(previousIds[i]);
	                	sessionFactory.openSession().insert("insertCurrentPriviousStep", data);
	                }
            	} catch (Exception e) {
            		throw new StoreException("Exception in add prev steps", e);
            	}
            }
        }
    }

    protected void cleanup(Connection connection, Statement statement, ResultSet result) {
        if (result != null) {
            try {
                result.close();
            } catch (SQLException ex) {
                log.error("Error closing resultset", ex);
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                log.error("Error closing statement", ex);
            }
        }

        if ((connection != null) && closeConnWhenDone) {
            try {
                connection.close();
            } catch (SQLException ex) {
                log.error("Error closing connection", ex);
            }
        }
    }

    protected long createCurrentStep(long entryId, int wfStepId, String owner, Date startDate, Date dueDate, String status) throws StoreException {
    	try {
    		WorkflowStepData data = new WorkflowStepData();
	        long id = getNextStepSequence();
	        log.debug("get new step id: " + id);
    		data.setId(id);
    		data.setEntryId(entryId);
    		data.setStepId(wfStepId);
    		data.setOwner(owner);
    		data.setStartDate(startDate);
    		data.setDueDate(dueDate);
    		data.setStatus(status);
    		sessionFactory.openSession().insert("insertCurrentStep", data);
    		
	        return id;
    	} catch (Exception e) {
    		throw new StoreException("Exception in create current step", e);
    	}
    	
    }

    ////////////METHOD #3 OF 3 //////////////////
    ////////// ...gur;  ////////////////////
    //kardes
    void doNestedNaturalJoin(WorkflowExpressionQuery e, NestedExpression nestedExpression, StringBuffer columns, StringBuffer where, StringBuffer whereComp, List values, List queries, StringBuffer orderBy) { // throws StoreException {

        Object value;
        int currentExpField;

        int numberOfExp = nestedExpression.getExpressionCount();

        for (int i = 0; i < numberOfExp; i++) { //ori

            //for (i = numberOfExp; i > 0; i--) { //reverse 1 of 3
            Expression expression = nestedExpression.getExpression(i); //ori

            //Expression expression = nestedExpression.getExpression(i - 1); //reverse 2 of 3
            if (!(expression.isNested())) {
                FieldExpression fieldExp = (FieldExpression) expression;

                FieldExpression fieldExpBeforeCurrent;
                queries.add(expression);

                int queryId = queries.size();

                if (queryId > 1) {
                    columns.append(" , ");
                }

                //do; OS_CURRENTSTEP AS a1 ....
                if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) {
                    columns.append("os_currentstep AS " + 'a' + queryId);
                } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) {
                    columns.append("os_historystep AS " + 'a' + queryId);
                } else {
                    columns.append("os_wfentry AS " + 'a' + queryId);
                }

                ///////// beginning of WHERE JOINS/s :   //////////////////////////////////////////
                //do for first query; a1.ENTRY_ID = a1.ENTRY_ID
                if (queryId == 1) {
                    where.append("a1.ENTRY_ID");
                    where.append(" = ");

                    if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) {
                        where.append("a" + queryId + ".ENTRY_ID");
                    } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) {
                        where.append("a" + queryId + ".ENTRY_ID");
                    } else {
                        where.append("a" + queryId + ".ID");
                    }
                }

                //do; a1.ENTRY_ID = a2.ENTRY_ID
                if (queryId > 1) {
                    fieldExpBeforeCurrent = (FieldExpression) queries.get(queryId - 2);

                    if (fieldExpBeforeCurrent.getContext() == FieldExpression.CURRENT_STEPS) {
                        where.append("a" + (queryId - 1) + ".ENTRY_ID");
                    } else if (fieldExpBeforeCurrent.getContext() == FieldExpression.HISTORY_STEPS) {
                        where.append("a" + (queryId - 1) + ".ENTRY_ID");
                    } else {
                        where.append("a" + (queryId - 1) + ".ID");
                    }

                    where.append(" = ");

                    if (fieldExp.getContext() == FieldExpression.CURRENT_STEPS) {
                        where.append("a" + queryId + ".ENTRY_ID");
                    } else if (fieldExp.getContext() == FieldExpression.HISTORY_STEPS) {
                        where.append("a" + queryId + ".ENTRY_ID");
                    } else {
                        where.append("a" + queryId + ".ID");
                    }
                }

                ///////// end of LEFT JOIN : "LEFT JOIN OS_CURRENTSTEP a1  ON a0.ENTRY_ID = a1.ENTRY_ID
                //
                //////// BEGINNING OF WHERE clause //////////////////////////////////////////////////
                value = fieldExp.getValue();
                currentExpField = fieldExp.getField();

                //if the Expression is negated and FieldExpression is "EQUALS", we need to negate that FieldExpression
                if (expression.isNegate()) {
                    //do ; a2.STATUS !=
                    whereComp.append("a" + queryId + '.' + fieldName(fieldExp.getField()));

                    switch (fieldExp.getOperator()) { //WHERE a1.STATUS !=
                    case FieldExpression.EQUALS:

                        if (value == null) {
                            whereComp.append(" IS NOT ");
                        } else {
                            whereComp.append(" != ");
                        }

                        break;

                    case FieldExpression.NOT_EQUALS:

                        if (value == null) {
                            whereComp.append(" IS ");
                        } else {
                            whereComp.append(" = ");
                        }

                        break;

                    case FieldExpression.GT:
                        whereComp.append(" < ");

                        break;

                    case FieldExpression.LT:
                        whereComp.append(" > ");

                        break;

                    default:
                        whereComp.append(" != ");

                        break;
                    }

                    switch (currentExpField) {
                    case FieldExpression.START_DATE:
                    case FieldExpression.FINISH_DATE:
                        values.add(new Timestamp(((java.util.Date) value).getTime()));

                        break;

                    default:

                        if (value == null) {
                            values.add(null);
                        } else {
                            values.add(value);
                        }

                        break;
                    }
                } else {
                    //do; a1.OWNER =
                    whereComp.append("a" + queryId + '.' + fieldName(fieldExp.getField()));

                    switch (fieldExp.getOperator()) { //WHERE a2.FINISH_DATE <
                    case FieldExpression.EQUALS:

                        if (value == null) {
                            whereComp.append(" IS ");
                        } else {
                            whereComp.append(" = ");
                        }

                        break;

                    case FieldExpression.NOT_EQUALS:

                        if (value == null) {
                            whereComp.append(" IS NOT ");
                        } else {
                            whereComp.append(" <> ");
                        }

                        break;

                    case FieldExpression.GT:
                        whereComp.append(" > ");

                        break;

                    case FieldExpression.LT:
                        whereComp.append(" < ");

                        break;

                    default:
                        whereComp.append(" = ");

                        break;
                    }

                    switch (currentExpField) {
                    case FieldExpression.START_DATE:
                    case FieldExpression.FINISH_DATE:
                        values.add(new Timestamp(((java.util.Date) value).getTime()));

                        break;

                    default:

                        if (value == null) {
                            values.add(null);
                        } else {
                            values.add(value);
                        }

                        break;
                    }
                }

                //do; a1.OWNER =  ?  ... a2.STATUS != ?
                whereComp.append(" ? ");

                //////// END OF WHERE clause////////////////////////////////////////////////////////////
                if ((e.getSortOrder() != WorkflowExpressionQuery.SORT_NONE) && (e.getOrderBy() != 0)) {
                    System.out.println("ORDER BY ; queries.size() : " + queries.size());
                    orderBy.append(" ORDER BY ");
                    orderBy.append("a1" + '.' + fieldName(e.getOrderBy()));

                    if (e.getSortOrder() == WorkflowExpressionQuery.SORT_ASC) {
                        orderBy.append(" ASC");
                    } else if (e.getSortOrder() == WorkflowExpressionQuery.SORT_DESC) {
                        orderBy.append(" DESC");
                    }
                }
            } else {
                NestedExpression nestedExp = (NestedExpression) expression;

                where.append('(');

                doNestedNaturalJoin(e, nestedExp, columns, where, whereComp, values, queries, orderBy);

                where.append(')');
            }

            //add AND or OR clause between the queries
            if (i < (numberOfExp - 1)) { //ori

                //if (i > 1) { //reverse 3 of 3
                if (nestedExpression.getExpressionOperator() == NestedExpression.AND) {
                    where.append(" AND ");
                    whereComp.append(" AND ");
                } else {
                    where.append(" OR ");
                    whereComp.append(" OR ");
                }
            }
        }
    }

    private String getInitProperty(Map props, String strName, String strDefault) {
        Object o = props.get(strName);

        if (o == null) {
            return strDefault;
        }

        return (String) o;
    }

    private String buildNested(NestedExpression nestedExpression, StringBuffer sel, List values) {
        sel.append("SELECT DISTINCT(");

        // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
        //sel.append(entryId);
        sel.append("ENTRY_ID AS retrieved");
        sel.append(") FROM ");

        // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
        // sel.append(entryTable);
        sel.append("os_currentstep");

        if (log.isDebugEnabled()) {
            log.debug("Thus far, query is: " + sel.toString());
        }

        for (int i = 0; i < nestedExpression.getExpressionCount(); i++) {
            Expression expression = nestedExpression.getExpression(i);

            if (i == 0) {
                sel.append(" WHERE ");
            } else {
                if (nestedExpression.getExpressionOperator() == NestedExpression.AND) {
                    sel.append(" AND ");
                } else {
                    sel.append(" OR ");
                }
            }

            if (expression.isNegate()) {
                sel.append(" NOT ");
            }

            // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
            // sel.append(entryId);
            sel.append("ENTRY_ID");
            sel.append(" IN (");

            if (expression.isNested()) {
                this.buildNested((NestedExpression) nestedExpression.getExpression(i), sel, values);
            } else {
                FieldExpression sub = (FieldExpression) nestedExpression.getExpression(i);
                this.buildSimple(sub, sel, values);
            }

            sel.append(')');
        }

        // Changed by Anthony on 2 June 2004, to query from OS_CURRENTSTEP instead
        // return (entryId);
        return ("ENTRY_ID");
    }

    private String buildSimple(FieldExpression fieldExpression, StringBuffer sel, List values) {
        String table;
        String columnName;

        if (fieldExpression.getContext() == FieldExpression.CURRENT_STEPS) {
            table = "os_currentstep";
            columnName = "ENTRY_ID";
        } else if (fieldExpression.getContext() == FieldExpression.HISTORY_STEPS) {
            table = "os_historystep";
            columnName = "ENTRY_ID";
        } else {
            table = "os_wfentry";
            columnName = "ID";
        }

        sel.append("SELECT DISTINCT(");
        sel.append(columnName);
        sel.append(") FROM ");
        sel.append(table);
        sel.append(" WHERE ");
        queryComparison(fieldExpression, sel, values);

        return columnName;
    }

    private List doExpressionQuery(String sel, String columnName, List values) throws StoreException {
        try {
        	List results = sessionFactory.openSession().selectList("queryWorkflowEntry", sel);
            return results;
        } catch (Exception ex) {
            throw new StoreException("SQL Exception in query: " + ex.getMessage());
        }
    }

    private static String escape(String s) {
        StringBuffer sb = new StringBuffer(s);

        char c;
        char[] chars = s.toCharArray();

        for (int i = 0; i < chars.length; i++) {
            c = chars[i];

            switch (c) {
            case '\'':
                sb.insert(i, '\'');
                i++;

                break;

            case '\\':
                sb.insert(i, '\\');
                i++;
            }
        }

        return sb.toString();
    }

    private String fieldName(int field) {
        switch (field) {
        case FieldExpression.ACTION: // actionId
            return "ACTION_ID";

        case FieldExpression.CALLER:
            return "CALLER";

        case FieldExpression.FINISH_DATE:
            return "FINISH_DATE";

        case FieldExpression.OWNER:
            return "OWNER";

        case FieldExpression.START_DATE:
            return "START_DATE";

        case FieldExpression.STEP: // stepId
            return "STEP_ID";

        case FieldExpression.STATUS:
            return "STATUS";

        case FieldExpression.STATE:
            return "STATE";

        case FieldExpression.NAME:
            return "NAME";

        case FieldExpression.DUE_DATE:
            return "DUE_DATE";

        default:
            return "1";
        }
    }

    private String queryComparison(WorkflowQuery query) {
        Object value = query.getValue();
        int operator = query.getOperator();
        int field = query.getField();

        //int type = query.getType();
        String oper;

        switch (operator) {
        case WorkflowQuery.EQUALS:
            oper = " = ";

            break;

        case WorkflowQuery.NOT_EQUALS:
            oper = " <> ";

            break;

        case WorkflowQuery.GT:
            oper = " > ";

            break;

        case WorkflowQuery.LT:
            oper = " < ";

            break;

        default:
            oper = " = ";
        }

        String left = fieldName(field);
        String right;

        if (value != null) {
            right = '\'' + escape(value.toString()) + '\'';
        } else {
            right = "null";
        }

        return left + oper + right;
    }

    /**
     * Method queryComparison
     *
     * @param    expression          a  FieldExpression
     * @param    sel                 a  StringBuffer
     *
     */
    private void queryComparison(FieldExpression expression, StringBuffer sel, List values) {
        Object value = expression.getValue();
        int operator = expression.getOperator();
        int field = expression.getField();

        String oper;

        switch (operator) {
        case FieldExpression.EQUALS:

            if (value == null) {
                oper = " IS ";
            } else {
                oper = " = ";
            }

            break;

        case FieldExpression.NOT_EQUALS:

            if (value == null) {
                oper = " IS NOT ";
            } else {
                oper = " <> ";
            }

            break;

        case FieldExpression.GT:
            oper = " > ";

            break;

        case FieldExpression.LT:
            oper = " < ";

            break;

        default:
            oper = " = ";
        }

        String left = fieldName(field);
        String right = "?";

        switch (field) {
        case FieldExpression.FINISH_DATE:
            values.add(new Timestamp(((Date) value).getTime()));

            break;

        case FieldExpression.START_DATE:
            values.add(new Timestamp(((Date) value).getTime()));

            break;

        case FieldExpression.DUE_DATE:
            values.add(new Timestamp(((Date) value).getTime()));

            break;

        default:

            if (value == null) {
                right = "null";
            } else {
                values.add(value);
            }
        }

        sel.append(left);
        sel.append(oper);
        sel.append(right);
    }

    private String queryWhere(WorkflowQuery query) {
        if (query.getLeft() == null) {
            // leaf node
            return queryComparison(query);
        } else {
            int operator = query.getOperator();
            WorkflowQuery left = query.getLeft();
            WorkflowQuery right = query.getRight();

            switch (operator) {
            case WorkflowQuery.AND:
                return '(' + queryWhere(left) + " AND " + queryWhere(right) + ')';

            case WorkflowQuery.OR:
                return '(' + queryWhere(left) + " OR " + queryWhere(right) + ')';

            case WorkflowQuery.XOR:
                return '(' + queryWhere(left) + " XOR " + queryWhere(right) + ')';
            }
        }

        return ""; // not sure if we should throw an exception or how this should be handled
    }    
}
